package com.gxuwz.lhleavesys.dao.impl;

import com.gxuwz.lhleavesys.dao.CourseDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.Course;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Repository
public class CourseDaoImpl implements CourseDao {
    @Autowired
    private DbUtil dbUtil;

    /**
     * 根据课程id查询课程信息
     * 
     * @param courseID
     * @return
     */
    @Override
    public Course findByCourseID(String courseID) {
        Course course = null;
        String sql = "SELECT * FROM sys_course WHERE courseID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, courseID);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    course = new Course(
                            rs.getString("courseID"),
                            rs.getString("classID"),
                            rs.getString("courseName"),
                            rs.getString("year"),
                            rs.getString("term"),
                            rs.getInt("hour"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return course;
    }

    /**
     * 查询所有课程信息
     * 
     * @return
     */
    @Override
    public List<Course> findAll() {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT * FROM sys_course";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Course course = new Course(
                        rs.getString("courseID"),
                        rs.getString("classID"),
                        rs.getString("courseName"),
                        rs.getString("year"),
                        rs.getString("term"),
                        rs.getInt("hour"));
                courses.add(course);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return courses;
    }

    /**
     * 新增课程
     * 
     * @param course
     * @return
     */
    @Override
    public boolean insert(Course course) {
        String sql = "INSERT INTO sys_course (courseID, classID, courseName, year, term, hour) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getCourseID());
            ps.setString(2, course.getClassID());
            ps.setString(3, course.getCourseName());
            ps.setString(4, course.getYear());
            ps.setString(5, course.getTerm());
            ps.setInt(6, course.getHour());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 修改班级信息
     * 
     * @param course
     * @return
     */
    @Override
    public boolean update(Course course) {
        String sql = "UPDATE sys_course SET  courseName = ? WHERE courseID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getCourseName());
            ps.setString(2, course.getCourseID());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 刪除课程信息
     * 
     * @param courseID
     * @return
     */
    @Override
    public boolean delete(String courseID) {
        String sql = "DELETE FROM sys_course WHERE courseID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, courseID);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean findBycourseID(String courseID) {
        boolean found = false;
        String sql = "SELECT COUNT(*) FROM sys_course WHERE courseID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, courseID);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    int count = rs.getInt(1);
                    found = count > 0;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return found;
    }

    @Override
    public boolean findByName(String courseName) {
        boolean found = false;
        String sql = "SELECT COUNT(*) FROM sys_course WHERE courseName = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, courseName);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    int count = rs.getInt(1);
                    found = count > 0;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return found;
    }

    @Override
    public List<Course> findAllorOne(String courseID, String courseName, String term) {
        List<Course> courses = new ArrayList<>();

        String sql = null;

        if ((courseID != null && !courseID.isEmpty()) && (courseName != null && !courseName.isEmpty())
                && (term != null && !term.isEmpty())) {// 什么都输
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? or courseName LIKE ? or term like ?";
        } else if ((courseID == null || courseID.equals("")) && (courseName != null && !courseName.isEmpty())
                && (term != null && !term.isEmpty())) {// 输入课程名称和学期
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? and courseName LIKE ? or term like ?";
        } else if ((courseID != null && !courseID.isEmpty()) && (courseName == null || courseName.equals(""))
                && (term != null && !term.isEmpty())) {// 输入课程编号和学期
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? and courseName LIKE ? or term like ?";
        } else if ((courseID != null && !courseID.isEmpty()) && (courseName != null && !courseName.isEmpty())
                && (term == null || term.equals(""))) {// 输入课程编号和课程名称
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? or courseName LIKE ? and term like ?";
        } else if ((courseID == null || courseID.equals("")) && (courseName == null || courseName.equals(""))
                && (term != null && !term.isEmpty())) {// 输入学期
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? and courseName LIKE ? and term like ?";
        } else if ((courseID == null || courseID.equals("")) && (courseName != null && !courseName.isEmpty())
                && (term == null || term.equals(""))) {// 输入课程名称
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? and courseName LIKE ? and term like ?";
        } else if ((courseID != null && !courseID.isEmpty()) && (courseName == null || courseName.equals(""))
                && (term == null || term.equals(""))) {// 输入课程编号
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? and courseName LIKE ? and term like ?";
        } else {// 都不输入
            sql = "SELECT * FROM sys_course WHERE courseID LIKE ? and courseName LIKE ? and term like ?";
        }
        try (Connection conn = dbUtil.getConn();

                PreparedStatement ps = conn.prepareStatement(sql)) {
            if (courseID != null && !courseID.isEmpty()) {
                ps.setString(1, "%" + courseID + "%");
            } else {
                ps.setString(1, "%"); // 占位符需要设置一个值，这里设置一个通配符表示匹配所有
            }
            if (courseName != null && !courseName.isEmpty()) {
                ps.setString(2, "%" + courseName + "%");
            } else {
                ps.setString(2, "%"); // 同样需要设置一个值
            }
            if (term != null && !term.isEmpty()) {
                ps.setString(3, "%" + term + "%");
            } else {
                ps.setString(3, "%"); // 同样需要设置一个值
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Course course = new Course();
                    course.setCourseID(rs.getString("courseID"));
                    course.setCourseName(rs.getString("courseName"));
                    course.setHour(rs.getInt("hour"));
                    course.setTerm(rs.getString("term"));
                    course.setYear(rs.getString("year"));
                    course.setClassID(rs.getString("classID"));
                    courses.add(course);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return courses;
    }

}
